package com.cpi.cornpp.customerpurchase.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.customer.exception.CustomerException;
import com.cpi.cornpp.customerpurchase.dto.CustomerPurchaseDTO;
import com.cpi.cornpp.futurepurchase.exception.FuturePurchaseException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class CustomerPurchaseDAO extends DBDAO {
	/** SQL select CustomerPurchase by customerid*/
	private static final String SQL_SELECT_CUSTOMER_PURCHASES_BY_CUSTOMER_ID = "SELECT corn_future_distribution_id, corn_future_id, customer_id, future_type_id, quantity, vw.description  FROM corn_futures_distributions, vw_customers vw WHERE cfd.customer_id = vw.customer_id and cfd.customer_id = ?";
	/** SQL select CustomerPurchase by corn_future_id*/
	private static final String SQL_SELECT_CUSTOMER_PURCHASES_BY_FUTURE_PURCHASE_ID = "SELECT cfd.corn_future_distribution_id, cfd.corn_future_id, cfd.customer_id, cfd.future_type_id, cfd.quantity, vw.description FROM corn_futures_distributions cfd, vw_customers vw WHERE cfd.customer_id = vw.customer_id and corn_future_id = ?";
	/** SQL insert CustomerPurchase*/
	private static final String SQL_INSERT_CUSTOMER_PURCHASE = "INSERT INTO corn_futures_distributions ( corn_future_id, customer_id, future_type_id, quantity) VALUES (?, ?, ?, ?)";	
	/** SQL delete CustomerPurchases by corn_future_distribution_id*/
	private static final String SQL_DELETE_CUSTOMER_PURCHASE_BY_ID = "DELETE FROM corn_futures_distributions WHERE corn_future_distribution_id = ?";
	/** SQL para obtener los productos de un cliente por su corn future distribution id*/
	private static final String SQL_SELECT_CUSTOMER_PURCHASES_BY_CORN_FUTURE_DISTRUBUTION_ID = "SELECT cfd.corn_future_distribution_id, cfd.corn_future_id, cfd.customer_id, cfd.future_type_id, cfd.quantity, vw.description FROM corn_futures_distributions cfd, vw_customers vw WHERE cfd.customer_id = vw.customer_id and cfd.corn_future_distribution_id = ?";	

	
	
	/**
	 * Insertar un CustomerPurchase
	 * @param cpDTO
	 * @return
	 * @throws FuturePurchaseException
	 */
	public int insert(CustomerPurchaseDTO cpDTO) throws FuturePurchaseException{
		int lastInserted = -1;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			
			if (cpDTO != null){
				pstm = prepareSQL(SQL_INSERT_CUSTOMER_PURCHASE, dsName);				
				
				pstm.setInt(1, cpDTO.getCornFutureId());
				pstm.setString(2, cpDTO.getCustomerId());
				pstm.setString(3, cpDTO.getFutureTypeId());
				pstm.setInt(4, cpDTO.getQuantity());
				
				executeInsert(pstm);
				closeConnection(pstm.getConnection());
				pstm = prepareSQL(getLastInsertIdForTable("corn_futures_distributions"),dsName); 
				rs = executeQuery(pstm);
				if(rs.next())
					lastInserted = rs.getInt(1);
			}
			
		}catch (PoolException poolException){
			FuturePurchaseException futurePurchaseException=null;
			poolException.printStackTrace(System.out);
			futurePurchaseException= new FuturePurchaseException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw futurePurchaseException;
		} catch(SQLException sqlException){
			FuturePurchaseException futurePurchaseException=null;
			sqlException.printStackTrace(System.out);
			futurePurchaseException= new FuturePurchaseException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw futurePurchaseException;
		} finally {
			try{
				
				closeConnection(pstm.getConnection());
				
			} catch(SQLException sqle){
				
			}			
		}
		
		return lastInserted;
	}
	
	/**
	 * Obtener un CustomerPurchase por su customerId
	 * @param cupDTO
	 * @return
	 * @throws FuturePurchaseException
	 */
	public List getCustomerPurchasesByCustomerId(CustomerPurchaseDTO cupDTO)throws FuturePurchaseException{
		List customerPurchases = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			
			if (cupDTO != null){
				pstm = prepareSQL(SQL_SELECT_CUSTOMER_PURCHASES_BY_CUSTOMER_ID, dsName);
				
				pstm.setString(1, cupDTO.getCustomerId());
				
				rs = executeQuery(pstm);
				
				customerPurchases = mapRows(rs);
			}
			
		}catch (PoolException poolException){
			FuturePurchaseException futurePurchaseException=null;
			poolException.printStackTrace(System.out);
			futurePurchaseException= new FuturePurchaseException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw futurePurchaseException;
		} catch(SQLException sqlException){
			FuturePurchaseException futurePurchaseException=null;
			sqlException.printStackTrace(System.out);
			futurePurchaseException= new FuturePurchaseException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw futurePurchaseException;
		} finally {
			try{
				
				closeConnection(pstm.getConnection());
				
			} catch(SQLException sqle){
				
			}
		}
		
		return customerPurchases;
	}
	
	public List getCustomerPurchasesByCornFutureDistrubutionId( int cornFutureDistributionId)throws FuturePurchaseException{
		List customerPurchases = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try{
			pstm = prepareSQL(SQL_SELECT_CUSTOMER_PURCHASES_BY_CORN_FUTURE_DISTRUBUTION_ID, dsName);
			pstm.setInt(1, cornFutureDistributionId);
			rs = executeQuery(pstm);
			customerPurchases = mapRows(rs);
		}catch (PoolException poolException){
			FuturePurchaseException futurePurchaseException=null;
			poolException.printStackTrace(System.out);
			futurePurchaseException= new FuturePurchaseException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw futurePurchaseException;
		} catch(SQLException sqlException){
			FuturePurchaseException futurePurchaseException=null;
			sqlException.printStackTrace(System.out);
			futurePurchaseException= new FuturePurchaseException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw futurePurchaseException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return customerPurchases;
	}
	
	/**
	 * Obtener un CustomerPurchase por su futurePurchaseId
	 * @param futurePurchaseId
	 * @return
	 * @throws FuturePurchaseException
	 */
	public List getCustomerPurchasesByFuturePuruchaseId(int futurePurchaseId)throws FuturePurchaseException{
		List customerPurchases = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			
			pstm = prepareSQL(SQL_SELECT_CUSTOMER_PURCHASES_BY_FUTURE_PURCHASE_ID, dsName);
			
			pstm.setInt(1, futurePurchaseId);
			
			rs = executeQuery(pstm);
			
			customerPurchases = mapRows(rs);
			
		}catch (PoolException poolException){
			FuturePurchaseException futurePurchaseException=null;
			poolException.printStackTrace(System.out);
			futurePurchaseException= new FuturePurchaseException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw futurePurchaseException;
		} catch(SQLException sqlException){
			FuturePurchaseException futurePurchaseException=null;
			sqlException.printStackTrace(System.out);
			futurePurchaseException= new FuturePurchaseException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw futurePurchaseException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		
		return customerPurchases;
	}
	
	
	
	/**
	 * Borrar CustomerPurchases basados en su corn_future_distribution_id
	 */
	public boolean delete(int customerPurchaseId) throws Exception {
		PreparedStatement preparedStatement = null;
		boolean result = false;
		try {
			preparedStatement = prepareSQL(SQL_DELETE_CUSTOMER_PURCHASE_BY_ID, dsName);
			preparedStatement.setInt(1, customerPurchaseId);
			executeUpdate(preparedStatement);

			result = true;
		} catch (SQLException sqlException) {
			CustomerException cpException = null;
			sqlException.printStackTrace(System.out);
			cpException = new CustomerException(sqlException,CustomerException.LAYER_DAO,	CustomerException.ACTION_DELETE);
			throw cpException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	
	
	/**
	 * Mapear masivamente los DTOs con los registros obtenidos
	 * de la tabla
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private List mapRows(ResultSet rs) throws SQLException{
		List customerPurchaseList = null;
		
		customerPurchaseList = new ArrayList(0);
		
		if (rs != null){
			
			for (; rs.next(); ){
				customerPurchaseList.add(mapRow(rs, rs.getRow()));
			}
		}
		
		return customerPurchaseList;
	}
	
	/**
	 * Mapear un DTO con el registro obtenido de la tabla
	 * @param rs
	 * @param rowIndex
	 * @return
	 * @throws SQLException
	 */
	private CustomerPurchaseDTO mapRow(ResultSet rs, int rowIndex) throws SQLException{
		CustomerPurchaseDTO customerPurchaseDTO = null;
		
		
		if ((rs != null) && (rs.absolute(rowIndex))){
			customerPurchaseDTO = new CustomerPurchaseDTO();
			
			customerPurchaseDTO.setCornFutureDistributionId(rs.getInt(1));
			customerPurchaseDTO.setCornFutureId(rs.getInt(2));
			customerPurchaseDTO.setCustomerId(rs.getString(3));
			customerPurchaseDTO.setFutureTypeId(rs.getString(4));
			customerPurchaseDTO.setQuantity(rs.getInt(5));
			customerPurchaseDTO.setDescription(rs.getString(6));
			
		}
		
		return customerPurchaseDTO;
	}
}